/*
 * Copyright (c) 2024. Shanghai HEADING information Engineering Co., Ltd. All rights reserved.
 */
package com.lovely602.design.study.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * h2文件数据库操作示例
 *
 * @author lizhixing
 */
public class H2FileExample {

  public static void main(String[] args) {
    try {
      Class.forName("org.h2.Driver");
      String url = "jdbc:h2:~/h2/test"; // 这将在用户目录的h2目录下创建一个名为'test'的数据库文件
      String username = "sa"; // 默认用户名
      String password = ""; // 默认密码
      Connection conn = DriverManager.getConnection(url, username, password);
      createTable(conn);
      insertData(conn);
      queryData(conn);
      updateData(conn);
      deleteData(conn);
      conn.close();
    } catch (ClassNotFoundException | SQLException e) {
      e.printStackTrace();
    } finally {
      System.out.println("H2数据库操作结束");
    }
  }

  private static void createTable(Connection conn) throws SQLException {
    String sqlCreateTable = "CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), age INT)";
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(sqlCreateTable);
    stmt.close();
  }

  private static void insertData(Connection conn) throws SQLException {
    String sqlInsertData = "INSERT INTO users (id, name, age) VALUES (1, 'Bob', 28)";
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(sqlInsertData);
    stmt.close();
  }

  private static void updateData(Connection conn) throws SQLException {
    String sqlUpdateData = "UPDATE users SET name = '张三' where id = 1";
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(sqlUpdateData);
    stmt.close();
  }

  private static void deleteData(Connection conn) throws SQLException {
    String sqlDeleteData = "DELETE FROM users WHERE id = 1";
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(sqlDeleteData);
    stmt.close();
  }

  private static void queryData(Connection conn) throws SQLException {
    String sqlQueryData = "select * from users";
    Statement stmt = conn.createStatement();
    ResultSet resultSet = stmt.executeQuery(sqlQueryData);
    while (resultSet.next()) {
      int id = resultSet.getInt("id");
      String name = resultSet.getString("name");
      int age = resultSet.getInt("age");
      System.out.println("id: " + id + ", name: " + name + ", age: " + age);
    }
    stmt.close();
  }

}
